﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Configuration;
using System.Web.UI;
using System.Data.SqlClient;
using System.Collections;
public class Datautility
{
    public Datautility()
    {

    }
    #region properties
    public static SqlConnection _con;
    public static SqlCommand _com;
    public static SqlTransaction _Transaction;

    public SqlConnection con
    {
        get { return _con; }
        set { _con = value; }
    }
    public SqlCommand com
    {
        get { return _com; }
        set { _com = value; }
    }
    #endregion
    #region private methods
    private void openconnection()
    {
        if (_con == null)
        {
            if (HttpContext.Current.Session["ConnStr"] != null)
            {
                _con = new SqlConnection(HttpContext.Current.Session["ConnStr"].ToString());
            }
            else
            {
                HttpContext.Current.Response.Redirect("~/SessionExpired.aspx");
            }
            _com = new SqlCommand();
            _com.Connection = _con;
        }
        if (_con.State == ConnectionState.Open)
        {
            _con.Close();
        }
        if (_con.State == ConnectionState.Closed)
        {
            _con.Open();
        }
    }
    private void closeconnection()
    {
        if (_con != null)
        {
            if (_con.State == ConnectionState.Open)
            {
                _con.Close();
            }
        }
    }
    private void disposeconnection()
    {
        if (_con != null)
        {
            if (_con.State == ConnectionState.Closed)
            {
                _con.Dispose();
                _con = null;
            }
        }
    }
    #endregion
    #region public methods
    public DataTable getdata(string strsql)
    {
        DataTable dt = new DataTable();
        try
        {
            openconnection();
            _com.CommandType = CommandType.Text;
            _com.CommandTimeout = 0;
            _com.CommandText = strsql;
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = _com;
            da.Fill(dt);
            closeconnection();
            disposeconnection();
        }
        catch (Exception ex)
        {
            closeconnection();
            disposeconnection();
        }
        finally
        {
            closeconnection();
            disposeconnection();
        }
        return dt;
    }
    public SqlDataReader getdatareader(string strsql)
    {
        openconnection();
        SqlDataReader dr = null;
        try
        {
            _com.CommandType = CommandType.Text;
            _com.CommandTimeout = 0;
            _com.CommandText = strsql;
            dr = com.ExecuteReader(CommandBehavior.CloseConnection);
        }
        catch (Exception ex)
        {
            closeconnection();
            disposeconnection();
        }
        finally
        {
            closeconnection();
            disposeconnection();
        }
        return dr;
    }
    public DataSet getdataset(string strsql)
    {
        openconnection();
        DataSet ds = new DataSet();
        try
        {
            _com.CommandType = CommandType.Text;
            _com.CommandTimeout = 0;
            _com.CommandText = strsql;            
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = _com;
            da.Fill(ds);
        }
        catch (Exception Ex)
        {
            closeconnection();
            disposeconnection();
        }
        finally
        {
            closeconnection();
            disposeconnection();
        }
        return ds;
    }
    public int executesql(string strsql)
    {
        openconnection();
        int result = 0;
        try
        {
            _com.CommandType = CommandType.Text;
            _com.CommandTimeout = 0;
            _com.CommandText = strsql;
            result = _com.ExecuteNonQuery();
        }
        catch (Exception Ex)
        {
            closeconnection();
            disposeconnection();
        }
        finally
        {
            closeconnection();
            disposeconnection();
        }
        return result;
    }
    public int executeprocedure(string spname, SqlParameter[] arrparam)
    {
        openconnection();
        int result=0;
        try
        {
            _com.CommandType = CommandType.StoredProcedure;
            _com.CommandTimeout = 0;
            _com.CommandText = spname;
            if (arrparam != null)
            {
                _com.Parameters.Clear();

                for (int i = 0; i < arrparam.Length; i++)
                {
                    _com.Parameters.Add(arrparam[i]);
                }
            }
            result = _com.ExecuteNonQuery();
        }
        catch (Exception Ex)
        {
            closeconnection();
            disposeconnection();
        }
        finally
        {
            closeconnection();
            disposeconnection();
        }
        return result;
    }
    public int executeprocedure1(string spname, SqlParameter[] arrparam)
    {
        openconnection();
        int result = 0;int r=0;
        try
        {
            _com.CommandType = CommandType.StoredProcedure;
            _com.CommandTimeout = 0;
            _com.CommandText = spname;
            if (arrparam != null)
            {
                _com.Parameters.Clear();

                for (int i = 0; i < arrparam.Length; i++)
                {
                    _com.Parameters.Add(arrparam[i]);

                }
            }
            result = _com.ExecuteNonQuery();
            SqlDataReader dr = _com.ExecuteReader();
            r = (int)dr.GetValue(0);
        }
        catch (Exception Ex)
        {
            closeconnection();
            disposeconnection();
        }
        finally
        {
            closeconnection();
            disposeconnection();
        }
        return r;
    }
    public string ExecuteProcedure_Scope_Identity(string spname, SqlParameter[] arrparam)
    {
        openconnection();
        string strResult = string.Empty;
        try
        {
            _com.CommandType = CommandType.StoredProcedure;
            _com.CommandTimeout = 0;
            _com.CommandText = spname;
            if (arrparam != null)
            {
                _com.Parameters.Clear();

                for (int i = 0; i < arrparam.Length; i++)
                {
                    _com.Parameters.Add(arrparam[i]);

                }
            }           
            SqlDataReader dr = _com.ExecuteReader(CommandBehavior.CloseConnection);
            if (dr.HasRows)
            {
                dr.Read();
                strResult = dr[0].ToString();
            }
        }
        catch (Exception Ex)
        {
            closeconnection();
            disposeconnection();
        }
        finally
        {
            closeconnection();
            disposeconnection();
        }
        return strResult;
    }
    public DataTable getdatausingprocedure(string spname)
    {
        openconnection();
        DataTable dt = new DataTable();
        try
        {
            _com.CommandType = CommandType.StoredProcedure;
            _com.CommandTimeout = 0;
            _com.CommandText = spname;            
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = _com;
            da.Fill(dt);
        }
        catch (Exception Ex)
        {
            closeconnection();
            disposeconnection();
        }
        finally
        {
            closeconnection();
            disposeconnection();
        }
        return dt;
    }
    public DataTable getdatausingprocedure(string spname, SqlParameter[] arrparam)
    {
        DataTable dt = new DataTable();
        try
        {
            openconnection();
            _com.CommandType = CommandType.StoredProcedure;
            _com.CommandTimeout = 0;
            _com.CommandText = spname;
            SqlDataAdapter da = new SqlDataAdapter();
            if (arrparam != null)
            {
                _com.Parameters.Clear();
                foreach (SqlParameter param in arrparam)
                {
                    _com.Parameters.Add(param);
                }
            }
            da.SelectCommand = _com;
            da.Fill(dt);
            _com.Parameters.Clear();
        }
        catch (Exception ex)
        {
            closeconnection();
            disposeconnection();
        }
        finally
        {
            closeconnection();
            disposeconnection();
        }
        return dt;
    }
    public SqlDataReader GetDataReaderUsingProcedure(string spname, SqlParameter[] arrparam)
    {
        openconnection();
        SqlDataReader dr=null;
        try
        {
            _com.CommandType = CommandType.StoredProcedure;
            _com.CommandTimeout = 0;
            _com.CommandText = spname;
            if (arrparam != null)
            {
                _com.Parameters.Clear();
                foreach (SqlParameter param in arrparam)
                {
                    _com.Parameters.Add(param);
                }
            }
            dr= com.ExecuteReader(CommandBehavior.CloseConnection);
            _com.Parameters.Clear();
        }
        catch (Exception Ex)
        {
            closeconnection();
            disposeconnection();
        }
        finally
        {
            closeconnection();
            disposeconnection();
        }
        return dr;
    }
    public SqlDataReader GetDataReaderUsingProcedure(string spname)
    {
        openconnection();
        SqlDataReader dr = null;
        try
        {
            _com.CommandType = CommandType.StoredProcedure;
            _com.CommandTimeout = 0;
            _com.CommandText = spname;
            dr = com.ExecuteReader(CommandBehavior.CloseConnection);
        }
        catch (Exception Ex)
        {
            closeconnection();
            disposeconnection();
        }
        finally
        {
            closeconnection();
            disposeconnection();
        }
        return dr;
    }
    public bool isexist(string strsql)
    {
        openconnection(); int result = 0;
        try
        {
            _com.CommandType = CommandType.Text;
            _com.CommandTimeout = 0;
            _com.CommandText = strsql;
            result = (int)_com.ExecuteScalar();
        }
        catch (Exception ex)
        {
            closeconnection();
            disposeconnection();
        }
        finally
        {
            closeconnection();
            disposeconnection();
        }
        if (result > 0)
        {
            return true;
        }
        else
        {
            return false;
        }
    }    
    #endregion
    #region Transaction
    public void TransactionStart()
    {
        openconnection();
        _Transaction = _con.BeginTransaction();
    }
    public void TransactionCommit()
    {
        try
        {
            if (_Transaction != null)
            {
                _Transaction.Commit();
            }        
        }
        catch (Exception ex)
        {

        }
        finally
        {
            if (_con.State==ConnectionState.Open)
            {
                closeconnection();
                disposeconnection();
            }
        }
    }
    public void TransactionRollBack()
    {
        if (_Transaction != null)
        {
            _Transaction.Rollback();
            _Transaction.Dispose();
            _Transaction = null;
        }
        closeconnection();
        disposeconnection();
    }
    public void TransactionStop()
    {
        closeconnection();
        disposeconnection();
        _Transaction = null;
    }
    #endregion
    #region TransactionMethods
    public object TranExecuteScalar(string squery)
    {
        object ObjResult;
        _com.CommandText = squery;
        _com.CommandTimeout = 0;
        _com.CommandType = CommandType.Text;
        _com.Transaction = _Transaction;
        ObjResult = _com.ExecuteScalar();
        return ObjResult;
    }
    public int TranExecuteSql(string squery)
    {
        int Result = 0;
        openconnection();
        SqlTransaction _Tran = _con.BeginTransaction();
        try
        {
            _com.CommandText = squery;
            _com.CommandTimeout = 0;
            _com.CommandType = CommandType.Text;
            _com.Transaction = _Tran;
            Result = _com.ExecuteNonQuery();
            _Tran.Commit();
        }
        catch
        {
            _Tran.Rollback();
        }
        finally
        {
            closeconnection();
            disposeconnection();
        }
        return Result;
    }
    public bool TranIsExist(string stringSql)
    {
        openconnection();
        _com.CommandType = CommandType.Text;
        _com.CommandTimeout = 0;
        _com.Transaction = _Transaction;
        _com.CommandText = stringSql;
        int result = (int)_com.ExecuteScalar();
        disposeconnection();
        if (result > 0)
        {
            return true;
        }
        else
        {
            return false;
        }
    }
    public int TranExecProcedure(string ProcName, SqlParameter[] sqlparam, SqlTransaction Tran)
    {
        int result = 0;
        openconnection();
        _com.CommandType = CommandType.StoredProcedure;
        _com.CommandText = ProcName;
        _com.CommandTimeout = 0;
        _com.Transaction = Tran;
        if (sqlparam != null)
        {
            _com.Parameters.Clear();
            foreach (SqlParameter Para in sqlparam)
            {
                _com.Parameters.Add(Para);
            }
        }
        result = _com.ExecuteNonQuery();
        _com.Parameters.Clear();
        closeconnection();
        disposeconnection();
        return result;
    }
    public int TranExecProcedure(string ProcName, SqlParameter[] sqlparam)
    {
        int result = 0;
        TransactionStart();
        _com.CommandType = CommandType.StoredProcedure;
        _com.CommandText = ProcName;
        _com.CommandTimeout = 0;
        _com.Transaction = _Transaction;
        if (sqlparam != null)
        {
            _com.Parameters.Clear();
            foreach (SqlParameter Para in sqlparam)
            {
                _com.Parameters.Add(Para);
            }
        }
        result = _com.ExecuteNonQuery();
        _com.Parameters.Clear();
        if (result > 0)
        {
            TransactionCommit();
            TransactionStop();
        }
        else
        {
            TransactionRollBack();
        }
        return result;
    }
    public DataTable TranGetDataTable(string StringQuery)
    {
        openconnection(); 
        SqlTransaction _Tran = _con.BeginTransaction();
        DataTable DT = new DataTable();
        try
        {                      
            _com.CommandText = StringQuery;
            _com.CommandTimeout = 0;
            _com.CommandType = CommandType.Text;
            _com.Transaction = _Tran;
            SqlDataAdapter DA = new SqlDataAdapter(_com);
            DA.Fill(DT);
            _Tran.Commit();
        }
        catch
        {
            _Tran.Rollback();
        }
        finally
        {
            closeconnection();
            disposeconnection();
        }
        return DT;
    }
    public DataSet TranGetDataSet(string StringQuery)
    {
        DataSet DS = new DataSet();
        _com.CommandText = StringQuery;
        _com.CommandTimeout = 0;
        _com.CommandType = CommandType.Text;
        _com.Transaction = _Transaction;
        SqlDataAdapter DA = new SqlDataAdapter(_com);
        DA.Fill(DS);
        return DS;
    }
    public DataTable TranGetDataFromProc(string ProcName)
    {
        DataTable DT = new DataTable();
        _com.CommandText = ProcName;
        _com.CommandTimeout = 0;
        _com.CommandType = CommandType.StoredProcedure;
        _com.Transaction = _Transaction;
        SqlDataAdapter DA = new SqlDataAdapter(_com);
        DA.Fill(DT);
        return DT;
    }
    public DataTable TranGetDataFromProc(string ProcName, SqlParameter[] Param)
    {
        DataTable DT = new DataTable();
        _com.CommandText = ProcName;
        _com.CommandTimeout = 0;
        _com.CommandType = CommandType.StoredProcedure;
        _com.Transaction = _Transaction;
        try
        {
            if (Param != null)
            {
                _com.Parameters.Clear();
                foreach (SqlParameter Para in Param)
                {
                    _com.Parameters.Add(Para);
                }
            }
            SqlDataAdapter DA = new SqlDataAdapter(_com);
            DA.Fill(DT);
        }
        catch (Exception ex)
        {
            _Transaction.Rollback();
        }
        _com.Parameters.Clear();
        _Transaction.Commit();
        return DT;
    }
    public SqlDataReader TranGetDataReader(string StringQuery)
    {
        _com.CommandText = StringQuery;
        _com.CommandTimeout = 0;
        _com.CommandType = CommandType.StoredProcedure;
        _com.Transaction = _Transaction;
        SqlDataReader DR = _com.ExecuteReader(CommandBehavior.CloseConnection);
        return DR;
    }
    #endregion
}
